[アップデート] BigQuery で待望のカラムへのデフォルト値設定が可能になりました！（プレビュー）

ウィスキー、シガー、パイプをこよなく愛する大栗です。

BigQuery でカラムのデフォルト値を設定することが可能になりました！DWH はデータをインポートするときに一部のカラムに値が入らない場合などがあり、インポート後に NULL のカラムに対して一定の値を設定するとことが有ると思いますが、今回のアップデートでインポート後の値設定のいち部が不要になると思われます。

• BigQuery release notes August 17, 2022
• Specify default column values

BigQuery のカラムに対するデフォルト値設定は2022年8月18日現在において、プレビューのステータスです。このプロダクトまたは機能は、Google Cloud Platform の利用規約の一般提供前のサービス規約の対象となります。一般提供前のプロダクトと機能では、サポートが制限されることがあります。また、一般提供前のプロダクトや機能に変更が加えられると、他の一般提供前バージョンと互換性がない場合があります。詳細については、リリースステージの説明をご覧ください。

カラムのデフォルト値

カラムのデフォルト値は、テーブルに新規にレコードを追加した時に値を設定していない場合に自動的に値を設定する機能です。一般的な RDBMS と同様に更新時に値は設定/更新されません。

カラムのデフォルト値は、リテラルか以下の関数のいずれかで有る必要があります。

• CURRENT_DATE
• CURRENT_DATETIME
• CURRENT_TIME
• CURRENT_TIMESTAMP
• GENERATE_UUID
• RAND
• SESSION_USER
• ST_GEOGPOINT

デフォルト値の設定はCREATE TABLE文とALTER TABLE文で設定できます。また、ALTER TABLE文でデフォルト値の設定を削除することも可能です。

やってみた

ここではbqコマンドを使用して実行しますが、Cloud Console の BigQuery UI 上で同様のクエリを実行しても同じ結果となります。

テーブル作成時のデフォルト値設定

$ bq query --use_legacy_sql=false 'CREATE SCHEMA default_value_dataset;'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (0s) Current status: DONE   

num1とstr1にデフォルト値を設定したテーブルを作成します。

$ bq query --use_legacy_sql=false 'CREATE TABLE default_value_dataset.foo
  (
    num1 int     default 9999,
    str1 string  default "Hello",
    d    datetime    
  );'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (0s) Current status: DONE
Created myproject.default_value_dataset.foo

レコードを挿入すると値を設定していないstr1にデフォルト値のHelloが入っています。

$ bq query --use_legacy_sql=false 'INSERT INTO `default_value_dataset.foo` (num1)
 VALUES (1);'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (1s) Current status: DONE   
Number of affected rows: 1

$ bq query --use_legacy_sql=false 'SELECT * FROM `default_value_dataset.foo`;'
+------+-------+------+
| num1 | str1  |  d   |
+------+-------+------+
|    1 | Hello | NULL |
+------+-------+------+

str1にNULLを設定するとデフォルトは設定されずNULLが設定されます。

$ bq query --use_legacy_sql=false 'INSERT INTO `default_value_dataset.foo` (num1, str1)
 VALUES (2, NULL);'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (1s) Current status: DONE   
Number of affected rows: 1

$ bq query --use_legacy_sql=false 'SELECT * FROM `default_value_dataset.foo`;'
+------+-------+------+
| num1 | str1  |  d   |
+------+-------+------+
|    2 | NULL  | NULL |
|    1 | Hello | NULL |
+------+-------+------+

既存カラムのデフォルト値設定

次に既存カラムのdにデフォルト値を設定してみます。以下のクエリでdカラムにデフォルトで現在時刻を設定します。

$ bq query --use_legacy_sql=false 'ALTER TABLE `default_value_dataset.foo`
 ALTER COLUMN d SET DEFAULT CURRENT_DATETIME();'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (0s) Current status: DONE
Altered myproject.default_value_dataset.foo

もう一度レコードを挿入してみます。今度はdカラムに現在時刻が設定されています。

$ bq query --use_legacy_sql=false 'INSERT INTO `default_value_dataset.foo` (num1)
 VALUES (3);'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (1s) Current status: DONE   
Number of affected rows: 1

$ bq query --use_legacy_sql=false 'SELECT * FROM `default_value_dataset.foo`;'
+------+-------+----------------------------+
| num1 | str1  |             d              |
+------+-------+----------------------------+
|    1 | Hello |                       NULL |
|    2 | NULL  |                       NULL |
|    3 | Hello | 2022-08-18T08:31:23.863404 |
+------+-------+----------------------------+

デフォルト値の評価タイミングの確認

dカラムには非決定的なCURRENT_DATETIME()をデフォルト値で設定しています。そのため大量のレコードを挿入した時に最初のレコードと最後のレコードの値がそうなるのか挙動を確認したいと思います。

ここで 6,000 万レコード近くあるテーブルであるbigquery-public-data.new_york_citibike.citibike_tripsを使用します。

$ bq query --use_legacy_sql=false 'SELECT COUNT(*)
 FROM `bigquery-public-data.new_york_citibike.citibike_trips`;'
+----------+
|   f0_    |
+----------+
| 58937715 |
+----------+

大量のレコードを挿入してみます。

$ bq query --use_legacy_sql=false 'INSERT INTO `default_value_dataset.foo` (num1)
 SELECT start_station_id 
 FROM `bigquery-public-data.new_york_citibike.citibike_trips`;'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (5s) Current status: DONE   
Number of affected rows: 58937715

dカラムの値を確認してみます。すると先程 5 秒程度かかって挿入したデータですがdカラムには同一の値が設定されています。つまりクエリ実行時に1回だけデフォルト値を評価していると思われます。

$ bq query --use_legacy_sql=false 'SELECT d, COUNT(*)
 FROM `default_value_dataset.foo`
 GROUP BY d;'
+----------------------------+----------+
|             d              |   f0_    |
+----------------------------+----------+
|                       NULL |        2 |
| 2022-08-18T08:31:23.863404 |        1 |
| 2022-08-18T08:41:47.760857 | 58937715 |
+----------------------------+----------+

デフォルト値の設定削除

デフォルト値の設定を削除してみます。

$ bq query --use_legacy_sql=false 'ALTER TABLE `default_value_dataset.foo2` ALTER COLUMN str1 DROP DEFAULT;'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (0s) Current status: DONE
Altered myproject.default_value_dataset.foo

レコードを挿入して確かめてみます。str1カラムがNULLになっておりデフォルト値の設定が削除されていることが確認できました。

$ bq query --use_legacy_sql=false 'INSERT INTO `default_value_dataset.foo` (num1, d)
 VALUES (0, "2000-01-01");'
Waiting on bqjob_123456789abcdef01_123456789abcedf0_1 ... (0s) Current status: DONE   
Number of affected rows: 1

$ bq query --use_legacy_sql=false 'SELECT *
 FROM `default_value_dataset.foo`
 WHERE d = "2000-01-01";'
+------+------+---------------------+
| num1 | str1 |          d          |
+------+------+---------------------+
|    0 | NULL | 2000-01-01T00:00:00 |
+------+------+---------------------+

さいごに

デフォルト値はデータベースとしては結構基本的な機能だと思っていました。そのため当然有るものだと考えていたため、今回のアップデートを確認して今まで無かったと知り、不思議に思いました。デフォルト値は無ければ無いでどうにかできるものですが、デフォルト値が設定できるので余計な手間が省けるようになったかなと思います。